package com.sl.au.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.sl.au.entity.ExdeBeforeContract;
import com.sl.au.entity.ExdeBeforeContractDetailParts;
import com.sl.au.entity.ExdeBeforeContractDetails;
import com.sl.au.entity.ExdeBeforeContractNotices;
import com.sl.au.entity.ExdeBeforeContractSpecialClause;
import com.sl.au.entity.ExdeBeforeContractWGDetail;
import com.sl.au.entity.User;

/***
 * 
 * @author chuck
 * 20170221 签订前要点审批DAO
 */
public interface ExdeBeforeContractRepository extends JpaRepository<ExdeBeforeContract, String>{
	
	@Query("select count(u) from User u where u.name like '%'+?1+'%'  and ( u.saleArea.areaName like '%运行%' or u.saleArea.areaName like '%设计%' or u.position like '%成套%' or u.saleArea.areaName like '%采购%' or u.saleArea.areaName like '%财务%')")
	long getAllUserCount(String key);
	
	/**
	 * 获取指定部门人员
	 * 
	 * @param superior
	 *            部门
	 * @return
	 */
	@Query("select u from User u where u.name like '%'+?1+'%' and ( u.saleArea.areaName like '%运行%' or u.saleArea.areaName like '%设计%' or  u.position like '%成套%' or u.saleArea.areaName like '%采购%' or u.saleArea.areaName like '%财务%')")
	Page<User> getAllUserList(String key,Pageable pageable);
	/**
	 * 获取所有人员
	 * 
	 * @param superior
	 *            部门
	 * @return
	 */
	@Query("select u from User u where u.name like '%'+?1+'%'")
	Page<User> getUserList(String key,Pageable pageable);
	@Query("select count(u) from User u where u.name like '%'+?1+'%' ")
	long getUserCount(String key);
	
	/***
	 * 查询全部
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query("select a from ExdeBeforeContract a where a.cContractCode like '%'+?1+'%' or a.cOrderUnitName like '%'+?1+'%' "
			+" or a.cDutyPersonName like '%'+?1+'%' or a.cProjectName like '%'+?1+'%' or a.auditState like '%'+?1+'%' or a.cCounterSignState like '%'+?1+'%' ")
	Page<ExdeBeforeContract> getBeforeContractsByAll(String key,Pageable pageable);
	
	/***
	 * 查询全部Count
	 * @param key
	 * @return
	 */
	@Query("select count(a) from ExdeBeforeContract a where a.cContractCode like '%'+?1+'%' or a.cOrderUnitName like '%'+?1+'%' "
			+" or a.cDutyPersonName like '%'+?1+'%' or a.cProjectName like '%'+?1+'%'  or a.auditState like '%'+?1+'%' or a.cCounterSignState like '%'+?1+'%' ")
	long getBeforeContractsCountByAll(String key);
	
	/***
	 * 查询本部门
	 * @param key
	 * @param superior
	 * @param pageable
	 * @return
	 */
	@Query("select a from ExdeBeforeContract a where (a.cContractCode like '%'+?1+'%' or a.cOrderUnitName like '%'+?1+'%' "
			+" or a.cDutyPersonName like '%'+?1+'%' or a.cProjectName like '%'+?1+'%'  or a.auditState like '%'+?1+'%' or a.cCounterSignState like '%'+?1+'%') and a.user.saleArea.superior=?2 ")
	Page<ExdeBeforeContract> getBeforeContractsByDept(String key,String superior,Pageable pageable);
	
	/***
	 * 查询本部门Count
	 * @param key
	 * @param superior
	 * @return
	 */
	@Query("select count(a) from ExdeBeforeContract a where (a.cContractCode like '%'+?1+'%' or a.cOrderUnitName like '%'+?1+'%' "
			+" or a.cDutyPersonName like '%'+?1+'%' or a.cProjectName like '%'+?1+'%'  or a.auditState like '%'+?1+'%' or a.cCounterSignState like '%'+?1+'%') and a.user.saleArea.superior=?2 ")
	long getBeforeContractsCountByDept(String key,String superior);
	
	/***
	 * 查询本片区
	 * @param key
	 * @param areaName
	 * @param pageable
	 * @return
	 */
	@Query("select a from ExdeBeforeContract a where (a.cContractCode like '%'+?1+'%' or a.cOrderUnitName like '%'+?1+'%' "
			+" or a.cDutyPersonName like '%'+?1+'%' or a.cProjectName like '%'+?1+'%'  or a.auditState like '%'+?1+'%' or a.cCounterSignState like '%'+?1+'%') and a.user.saleArea.areaName=?2  ")
	Page<ExdeBeforeContract> getBeforeContractsByArea(String key,String areaName,Pageable pageable);
	
	/***
	 * 查询本片区Count
	 * @param key
	 * @param areaName
	 * @return
	 */
	@Query("select count(a) from ExdeBeforeContract a where (a.cContractCode like '%'+?1+'%' or a.cOrderUnitName like '%'+?1+'%' "
			+" or a.cDutyPersonName like '%'+?1+'%' or a.cProjectName like '%'+?1+'%'  or a.auditState like '%'+?1+'%' or a.cCounterSignState like '%'+?1+'%') and a.user.saleArea.areaName=?2")
	long getBeforeContractsCountByArea(String key,String areaName);
	/***
	 * 查询本人
	 * @param key
	 * @param userId
	 * @param pageable
	 * @return
	 */
	@Query("select a from ExdeBeforeContract a where (a.cContractCode like '%'+?1+'%' or a.cOrderUnitName like '%'+?1+'%' "
			+" or a.cDutyPersonName like '%'+?1+'%' or a.cProjectName like '%'+?1+'%'  or a.auditState like '%'+?1+'%' or a.cCounterSignState like '%'+?1+'%') and a.user.id=?2 ")
	Page<ExdeBeforeContract> getBeforeContractsByPerson(String key,String userId,Pageable pageable);

	/***
	 * 查询本人Count
	 * @param key
	 * @param userId
	 * @return
	 */
	@Query("select count(a) from ExdeBeforeContract a where (a.cContractCode like '%'+?1+'%' or a.cOrderUnitName like '%'+?1+'%' "
			+" or a.cDutyPersonName like '%'+?1+'%' or a.cProjectName like '%'+?1+'%'  or a.auditState like '%'+?1+'%' or a.cCounterSignState like '%'+?1+'%') and a.user.id=?2 ")
	long getBeforeContractsCountByPerson(String key,String userId);
	
	/***
	 * 通过ID查询签订前要点审批主表信息
	 * @param id
	 * @return
	 */
	@Query("select c from ExdeBeforeContract c where c.id=? ")
	ExdeBeforeContract getBeforeContract(String id);
	
	/***
	 * 通过签订前审批标的物子表IDS删除对应指标明细
	 * @param id
	 */
	@Transactional
	@Modifying
	@Query("delete from ExdeBeforeContractDetailParts c where c.bContractDetailId=? ")
	void deleteExdeBeforeContractDetailPartsByBeforeContractDetailsId(String ids);
	
	/***
	 * 通过签订前审批ID删除合同签订前审批产品子表
	 * @param id
	 */
	@Transactional
	@Modifying
	@Query("delete from ExdeBeforeContractDetails c where c.bContractId=?")
	void deleteExdeBeforeContractDetailsByBeforeContractId(String id);
	
	
	/***
	 * 通过签订前审批ID删除合同签订前审批其它注意条款子表
	 * @param id
	 */
	@Transactional
	@Modifying
	@Query("delete from ExdeBeforeContractNotices c where c.bContractId=?")
	void deleteExdeBeforeContractNoticesByBeforeContractId(String id);
	
	/***
	 * 通过签订前审批ID删除合同签订前审批合同特殊条款子表
	 * @param id
	 */
	@Transactional
	@Modifying
	@Query("delete from ExdeBeforeContractSpecialClause c where c.bContractId=?")
	void deleteExdeBeforeContractSpecialClauseByBeforeContractId(String id);
	
	/***
	 * 通过签订前审批ID删除合同签订前审批外购件独家指定子表
	 * @param id
	 */
	@Transactional
	@Modifying
	@Query("delete from ExdeBeforeContractWGDetail c where c.bContractId=?")
	void deleteExdeBeforeContractWGDetailByBeforeContractId(String id);
	
	/***
	 * 通过签订前审批ID删除合同签订前审批主表
	 * @param id
	 */
	@Transactional
	@Modifying
	@Query("delete from ExdeBeforeContract c where c.id=?")
	void deleteExdeBeforeContractById(String id);
	
	/***
	 * 通过签订前要点审批ID查询合同签订前审批产品子表List
	 * @param id
	 * @return
	 */
	@Query("select c from ExdeBeforeContractDetails c where c.bContractId=?")
	Page<ExdeBeforeContractDetails> getExdeBeforeContractDetailsByBeforeId(String id,Pageable pageable);
	
	/***
	 * 通过签订前要点审批ID查询合同签订前审批产品子表Count
	 * @param id
	 * @return
	 */
	@Query("select count(c) from ExdeBeforeContractDetails c where c.bContractId=?")
	long getExdeBeforeContractDetailsCountByBeforeId(String id);
	
	/***
	 * 通过合同标的物子表Id查询合同签订前审批合同合同标的物子表的子表List
	 * @param id
	 * @param pageable
	 * @return
	 */
	@Query("select c from ExdeBeforeContractDetailParts c where c.bContractDetailId=?")
	Page<ExdeBeforeContractDetailParts> getExdeBeforeContractDetailPartsByDetailId(String id,Pageable pageable);
	
	/***
	 * 通过合同标的物子表Id查询合同签订前审批合同合同标的物子表的子表Count
	 * @param id
	 * @return
	 */
	@Query("select count(c) from ExdeBeforeContractDetailParts c where c.bContractDetailId=?")
	long getExdeBeforeContractDetailPartsCountByDetailId(String id);
	
	/***
	 * 通过签订前要点Id查询合同签订前审批其它注意条款子表List
	 * @param id
	 * @param pageable
	 * @return
	 */
	@Query("select c from ExdeBeforeContractNotices c where c.bContractId=?")
	Page<ExdeBeforeContractNotices> getExdeBeforeContractNoticesByBeforeId(String id,Pageable pageable);
	
	/***
	 * 通过签订前要点Id查询合同签订前审批其它注意条款子表Count
	 * @param id
	 * @return
	 */
	@Query("select count(c) from ExdeBeforeContractNotices c where c.bContractId=?")
	long getExdeBeforeContractNoticesCountByBeforeId(String id);
	
	/***
	 * 通过签订前要点Id查询合同签订前审批其它注意条款子表List
	 * @param id
	 * @param pageable
	 * @return
	 */
	@Query("select c from ExdeBeforeContractSpecialClause c where c.bContractId=?")
	Page<ExdeBeforeContractSpecialClause> getExdeBeforeContractSpecialClauseByBeforeId(String id,Pageable pageable);
	
	/***
	 * 通过签订前要点Id查询合同签订前审批其它注意条款子表Count
	 * @param id
	 * @return
	 */
	@Query("select count(c) from ExdeBeforeContractSpecialClause c where c.bContractId=?")
	long getExdeBeforeContractSpecialClauseCountByBeforeId(String id);
	
	/***
	 * 通过签订前要点Id查询合同签订前审批外购件独家指定子表List
	 * @param id
	 * @param pageable
	 * @return
	 */
	@Query("select c from ExdeBeforeContractWGDetail c where c.bContractId=?")
	Page<ExdeBeforeContractWGDetail> getExdeBeforeContractWGDetailByBeforeId(String id,Pageable pageable);
	
	/***
	 * 通过签订前要点Id查询合同签订前审批外购件独家指定子表Count
	 * @param id
	 * @return
	 */
	@Query("select count(c) from ExdeBeforeContractWGDetail c where c.bContractId=?")
	long getExdeBeforeContractWGDetailCountByBeforeId(String id);
	
	
}
